package com.jiguang.shangcheng.dao;

import com.jiguang.shangcheng.pojo.Order;
import com.jiguang.shangcheng.pojo.Product;
import com.jiguang.shangcheng.pojo.User;
import com.jiguang.shangcheng.utils.MD5;
import com.sun.org.apache.xpath.internal.operations.Or;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class ShangChengDao {
    //连接数据库
    Connection conn = BaseDao.getconn();

    @SneakyThrows
    public boolean login(String number,String password) throws Exception {
        if(conn != null){
            conn.close();
            conn = BaseDao.getconn();
        }
        //密码加密
        String pwd = MD5.md5(password);

        //判断账号密码是否正确
        String adminLogin_sql = "select count(*) from user where number= ? and password = ?";
        PreparedStatement pst = conn.prepareStatement(adminLogin_sql);
        pst.setString(1,number);
        pst.setString(2,pwd);

        ResultSet resultSet = pst.executeQuery();

        int count = 0;
        while (resultSet.next()){
            count = resultSet.getInt(1);
        }
        if(count>0) return true;

        return false;
    }

    //查询用户信息
    public User selectUser(String number, String password) throws Exception {
        User user = new User();
        //密码加密
        String pwd = MD5.md5(password);

        //查询数据库
        String selectUser_sql = "select * from user where number = ? and password = ?";
        PreparedStatement pst = conn.prepareStatement(selectUser_sql);
        pst.setString(1,number);
        pst.setString(2,pwd);
        ResultSet rs = pst.executeQuery();

        while (rs.next()){
            user = new User(
                    rs.getInt("id"),
                    rs.getString("number"),
                    rs.getString("netname"),
                    rs.getString("password"),
                    rs.getString("turename"),
                    rs.getString("bank_card"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("admin")
            );
        }

        return user;
    }

    //通过关键字查询信息
    public List<User> selectUserBykeyword(String keyword) throws Exception {
        List<User> users = new ArrayList<>();

        //分析查询
        String sellectAllUser_sql = "select * from user where netname like ?";
        PreparedStatement pst = conn.prepareStatement(sellectAllUser_sql);
        pst.setString(1,"%"+keyword+"%");

        ResultSet rs = pst.executeQuery();

        //获取用户信息
        while (rs.next()){
            User user = new User(
                    rs.getInt("id"),
                    rs.getString("number"),
                    rs.getString("netname"),
                    rs.getString("password"),
                    rs.getString("turename"),
                    rs.getString("bank_card"),
                    rs.getString("photo"),
                    rs.getString("state"),
                    rs.getString("admin")
            );
            //将数据加入集合
            users.add(user);
        }
        return users;
    }

    public boolean updatePwd(String number,String password) throws Exception {
        //密码加密
        String pwd = MD5.md5(password);

        String updatePwd_Sql = "update user set password = ? where number = ?";
        PreparedStatement ps = conn.prepareStatement(updatePwd_Sql);
        ps.setString(1,pwd);
        ps.setString(2,number);
        int i = ps.executeUpdate();
        if(i>0) return true;

        return false;
    }

    //查询账号是否重复
    public boolean selectNumber(String number) throws Exception {

        //判断账号密码是否正确
        String adminLogin_sql = "select count(*) from user where number= ?";
        PreparedStatement pst = conn.prepareStatement(adminLogin_sql);
        pst.setString(1,number);

        ResultSet resultSet = pst.executeQuery();

        int count = 0;
        while (resultSet.next()){
            count = resultSet.getInt(1);
        }
        if(count>0) return true;

        return false;
    }

    //注册
    public boolean insertNumber(User user) throws Exception {

        String inser_sql = "insert into user (number,netname,password,photo) values(?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(inser_sql);
        ps.setString(1,user.getNumber());
        ps.setString(2,user.getNetname());
        ps.setString(3,user.getPassword());
        ps.setString(4,user.getPhoto());
        int i = ps.executeUpdate();
        if(i>0) return true;

        return false;
    }


    //添加订单
    public boolean insertOrder(Order order) throws Exception {
        //获取当前时间
        Date date = new Date();
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.HOUR_OF_DAY,8);
        date = c.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        String inser_sql = "insert into userorder (user_id,product_id,create_time) values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(inser_sql);
        ps.setInt(1,order.getUserId());
        ps.setInt(2,order.getProductId());
        ps.setString(3,sdf.format(date));
        int i = ps.executeUpdate();
        if(i>0) return true;

        return false;
    }

    //修改用户信息
    public boolean updateUser(User user) throws Exception {
        //进行假删除，将账号状态设为2

        String deleteUser_sql = "update user set photo = ?,turename = ?,netname = ?,bank_card = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,user.getPhoto());
        ps.setString(2,user.getTurename());
        ps.setString(3,user.getNetname());
        ps.setString(4,user.getBankCard());
        ps.setInt(5,user.getId());
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }

    //添加商品
    public boolean insertProdutc(Product product) throws Exception {
        //获取当前时间
        Date date = new Date();
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        c.add(Calendar.HOUR_OF_DAY,8);
        date = c.getTime();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        String inser_sql = "insert into product(user_id,name,price,game,photo,create_time) values(?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(inser_sql);
        ps.setInt(1,product.getUserId());
        ps.setString(2,product.getName());
        ps.setString(3,product.getPrice());
        ps.setString(4,product.getGame());
        ps.setString(5,product.getPhoto());
        ps.setString(6,sdf.format(date));
        int i = ps.executeUpdate();
        if(i>0) return true;

        return false;
    }

    //查询我购卖的订单
    public List<Order> selectMyBuyOrder(Integer id) throws Exception {
        List<Order> orderList = new ArrayList<>();

        String selectMySole_sql = "select * from userorder where user_id = ? and state !=  ?";
        PreparedStatement ps = conn.prepareStatement(selectMySole_sql);
        ps.setInt(1,id);
        ps.setString(2,"4");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            Order order = new Order(
                    rs.getInt("id"),
                    rs.getInt("user_id"),
                    rs.getInt("product_id"),
                    rs.getString("state"),
                    rs.getString("create_time"),
                    rs.getString("paid")
            );
            orderList.add(order);
        }
        return orderList;
    }

    //通过订单ID修改订单状态
    public boolean updateOrderState(String id, String state) throws Exception {
        //将账号状态设为1

        String deleteUser_sql = "update userorder set state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,state);
        ps.setInt(2,Integer.parseInt(id));
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }

    //上传文件到数据库
    public boolean uplodaPaid(String fileName,String id) throws Exception {
        //将账号状态设为1

        String deleteUser_sql = "update userorder set paid = ?,state = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(deleteUser_sql);
        ps.setString(1,fileName);
        ps.setString(2,"2");
        ps.setInt(3,Integer.parseInt(id));
        int update = ps.executeUpdate();
        if(update>0) return true;
        return false;
    }
}
